from db_conn import pd_conn_wfw, pd_conn_zfw
import pandas as pd


def part_in(st, et):
    sql1 = """
        select f_ware_house_code 分仓手机号, f_ware_house_name 分仓合伙人 from t_factory_user 
        where f_ware_house_code is not null and f_status = 0
    """
    df_user = pd_conn_zfw(sql1)

    sql2 = f"""
        -- 接收日期	操作账号	接收虚拟仓	合伙人	物料编码	物料名称	规格型号	单位	数量
        SELECT   
            r.f_create_time 入库时间,r.f_mobile 分仓手机号,
            r.f_part_code 配件id,r.f_part_name 配件名称,
            g.f_part_specification 配件规格,f_part_Unit 配件单位,r.f_qty 配件数量
        FROM 
            t_stock_record r
            left join t_part_good g on g.f_part_code=r.f_part_code
        where r.f_type = 1 and r.f_create_time>='{st}' and r.f_create_time<'{et}'
    """
    df_part = pd_conn_wfw(sql2, 'ygl_stock')

    df = df_part.merge(df_user, on='分仓手机号', how='left')
    return df


def part_out(st, et):
    sql1 = """
        select f_ware_house_code 分仓手机号, f_ware_house_name 分仓合伙人 from t_factory_user 
        where f_ware_house_code is not null and f_status = 0
    """
    df_user = pd_conn_zfw(sql1)

    sql2 = f"""
        -- 更换日期	操作账号	合伙人	物料编码	物料名称	规格型号	单位	数量	站点	省	市	区
        SELECT   
            r.f_create_time 出库时间,r.f_mobile 分仓手机号,
            r.f_part_code 配件id,r.f_part_name 配件名称,
            g.f_part_specification 配件规格,f_part_Unit 配件单位,r.f_qty 配件数量,
            r.f_task_id
        FROM 
            t_stock_record r
            left join t_part_good g on g.f_part_code=r.f_part_code
        where r.f_type = 2 and r.f_task_id is not null
        and r.f_create_time>='{st}' and r.f_create_time<'{et}'
    """
    df_part = pd_conn_wfw(sql2, 'ygl_stock')

    sql3 = f"""
        SELECT f.f_id 站点id,f.f_name 站点名称,f.f_province 省,f.f_city 市,f.f_district 区, t.f_id f_task_id
        FROM t_task t left join t_factory f on f.f_id=t.f_factoryIds
    """
    df_factory = pd_conn_zfw(sql3)

    df = df_part.merge(df_user, how='left', on='分仓手机号').merge(df_factory, on='f_task_id')

    return df


# 库存
def part_stock():
    sql1 = """
        select f_ware_house_code 分仓手机号, group_concat(f_ware_house_name) 分仓合伙人 from t_factory_user 
        where f_ware_house_code is not null and f_status = 0 and f_mobile = f_ware_house_code
        group by f_ware_house_code
    """
    df_user = pd_conn_zfw(sql1)
    sql2 = """
    select f_mobile 分仓手机号, a.f_part_code,a.f_part_name,a.f_part_qty, b.f_part_Unit
    from t_user_stock a left join t_part_good b on a.f_part_code=b.f_part_code
    """
    df_stock = pd_conn_wfw(sql2, 'ygl_stock')
    df = df_stock.merge(df_user, on='分仓手机号', how='left')
    return df


if __name__ == '__main__':
    # df = part_out('2022-01-01', '2022-07-01')
    # df.to_csv('part_out.csv')
    # df = part_in('2022-01-01', '2022-07-01')
    # df.to_csv('part_in.csv')
    df = part_stock()
    df.to_excel('part_stock.xlsx')
